package com.yenlien.traceyun.control.dao;


import com.yenlien.traceyun.control.entity.ModelCrop;
import com.yenlien.traceyun.webase.dao.BaseJpaDao;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * Created by LiuSha on 2020/3/24.
 */
public interface ModelCropDao extends BaseJpaDao<ModelCrop> {
    ModelCrop findById(String id);

//    List<ModelCrop> findByOneId(String oneId);
//
//    List<ModelCrop> findByTwoId(String twoId);

    @Query(value = "select m.name from ModelCrop m where m.id=?1")
    String getByName(String id);

    public List<ModelCrop> findByNameLike(String name);

    public List<ModelCrop> findByCategoryIdInAndCompanyIdIsNull(List<String> ids);

    @Query(value = "select m from ModelCrop m where m.categoryId in(?1) and m.companyId is null and m.name like CONCAT('%',?2,'%') ")
    public List<ModelCrop> findByCategoryIds(List<String> ids, String name);

    @Query(value = "select m.id from ModelCrop m where m.categoryId in(?1)")
    public List<String> findIdsByCategoryIdIn(List<String> ids);

    public void deleteByCategoryIdIn(List<String> categoryId);

    @Transactional
    public void deleteByCategoryId(String categoryId);

    public void deleteByIdIn(List<String> id);

    @Transactional
    @Modifying
    @Query("update ModelCrop m set m.categoryId=?1 where m.categoryId=?2 ")
    public void updateCategoryId(String newId, String oldId);

    @Query(value = "select m from ModelCrop m where m.id in( select cc.modelId from CompanyCrop cc where cc.companyId=?1 and appId=?2) ")
    public List<ModelCrop> findByCompanyIdAndAppId(String companyId, String appId);

    @Query(value = "select m from ModelCrop m where m.id in( select cc.modelId from CompanyCrop cc where cc.companyId=?1) ")
    public List<ModelCrop> findByCompanyId(String companyId);

    @Query(value = "SELECT parent_id FROM t_company WHERE id = ?1", nativeQuery = true)
    String getParentId(String companyId);

    /**
     * 根据企业和一级分类查出该企业该分类下的基源
     *
     * @param companyId
     * @param categoryId
     * @return
     */
    @Query(value = "SELECT m.* FROM t_model_crop m WHERE m.id IN (SELECT DISTINCT tcc.model_id FROM t_company_crop tcc JOIN t_model_crop tmc ON tcc.model_id = tmc.id " +
            "JOIN t_model_category tm ON category_id = tm.id WHERE tcc.company_id = ?1 AND tm.parent_id = ?2 )", nativeQuery = true)
    List<ModelCrop> getJy(String companyId, String categoryId);

//    @Query(value = "SELECT * FROM t_model_crop WHERE category_id = ?1", nativeQuery = true)
//    List<ModelCrop> getByCategoryId(String categoryId);

    @Query(value = "SELECT tmc.* FROM t_model_crop tmc JOIN t_model_category tm ON tmc.category_id = tm.id WHERE tm.parent_id = ?1", nativeQuery = true)
    List<ModelCrop> getByCategoryId(String categoryId);
}
